import datetime

import pandas as pd
from django.db.models import Q, F, Sum
from pypinyin import pinyin, Style

from finance.api.permissions import FinancePermission
from rest_framework import status
from rest_framework.decorators import action
from rest_framework.generics import ListAPIView
from rest_framework.response import Response
from rest_framework.views import APIView
from rest_framework.viewsets import ModelViewSet, ReadOnlyModelViewSet

from .serializers import JobSerializer, AccountTitleSerializer, EmployeeSerializer, CustomerSerializer, BankSerializer, \
    AccountTitleListSerializer, CasePersonSerializer, CaseLawyerSerializer, SaleOrderSerializer, ReceiptSerializer, \
    CustomerListSerializer, SaleOrderListSerializer, ExpenseSerializer, ProfitRuleSerializer, OtherIncomeSerializer, \
    PaymentOrderSerializer, JournalSerializer, PaymentDetailSerializer, InitAccountSerializer, PayOrderSerializer, \
    BorrowLendSerializer, FundBillSerializer, RepaymentBillSerializer, AccountCodeSerializer, PayOrderCreateSerializer, \
    BusinessFeeSerializer
from finance.models import Job, AccountTitle, Employee, Customer, Bank, CasePerson, CaseLawyer, CASE_CATEGORY, \
    SaleOrder, Receipt, Expense, ProfitRule, OtherIncome, PaymentOrder, Journal, PaymentDetail, InitAccount, PayOrder, \
    BorrowLend, FundBill, RepaymentBill, AccountCode, BusinessFee


class CaseCategoryAPIView(APIView):
    """案件类型"""
    authentication_classes = ()

    def get(self, request, *args, **kwargs):
        dict_cate = [{"value": item[0], "label": item[1]} for item in CASE_CATEGORY]
        return Response(dict_cate)


class AccountCodeListAPIView(ListAPIView):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = AccountCode.objects.all()
    serializer_class = AccountCodeSerializer


class JobViewSet(ModelViewSet):
    authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = Job.objects.all()
    serializer_class = JobSerializer


class AccountCodeViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = AccountCode.objects.all()
    serializer_class = AccountCodeSerializer

    def get_queryset(self):
        queryset = super().get_queryset()

        name = self.request.query_params.get("name")
        if name:
            queryset = queryset.filter(Q(name__contains=name) | Q(code__contains=name))

        return queryset


class AccountTitleViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = AccountTitle.objects.all()
    serializer_class = AccountTitleSerializer

    def get_queryset(self):
        queryset = super(AccountTitleViewSet, self).get_queryset()
        if self.lookup_field in self.kwargs:
            return queryset

        name = self.request.query_params.get("name")
        if name:
            queryset = queryset.filter(name__contains=name)
        else:
            queryset = queryset.filter(parent=None)

        return queryset

    def get_serializer_class(self):
        name = self.request.query_params.get("name")
        if name:
            return AccountTitleListSerializer

        return super(AccountTitleViewSet, self).get_serializer_class()


class EmployeeViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = Employee.objects.all()
    serializer_class = EmployeeSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        if name:
            queryset = queryset.filter(Q(name__contains=name) | Q(telephone__contains=name))

        return queryset


class EmployeeListAPIView(ListAPIView):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = Employee.objects.all()
    serializer_class = EmployeeSerializer


class CustomerViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = Customer.objects.all()
    serializer_class = CustomerSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        if name:
            queryset = queryset.filter(Q(name__contains=name) | Q(telephone__contains=name) | Q(case_name__contains=name))

        return queryset


class CustomerListAPIView(ListAPIView):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = Customer.objects.all()
    serializer_class = CustomerListSerializer


class CasePersonViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = CasePerson.objects.all()
    serializer_class = CasePersonSerializer


class CaseLawyerViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = CaseLawyer.objects.all()
    serializer_class = CaseLawyerSerializer

    def create(self, request, *args, **kwargs):
        if isinstance(request.data, list):  # 批量增加
            serializer = self.get_serializer(data=request.data, many=True)
        else:
            serializer = self.get_serializer(data=request.data)

        serializer.is_valid(raise_exception=True)
        self.perform_create(serializer)
        headers = self.get_success_headers(serializer.data)
        return Response(serializer.data, status=status.HTTP_201_CREATED, headers=headers)

    def update(self, request, *args, **kwargs):
        partial = kwargs.pop('partial', False)
        if isinstance(request.data, list):
            data = request.data
            ids = [item["id"] for item in data if item.get("id", None)]
            queryset = self.serializer_class.Meta.model.objects.filter(id__in=ids)
            serializer = self.get_serializer(queryset, data=request.data, partial=True, many=True)
            serializer.is_valid(raise_exception=True)
            self.perform_update(serializer)
        else:
            instance = self.get_object()
            serializer = self.get_serializer(instance, data=request.data, partial=partial)
            serializer.is_valid(raise_exception=True)
            self.perform_update(serializer)

            if getattr(instance, '_prefetched_objects_cache', None):
                instance._prefetched_objects_cache = {}

        return Response(serializer.data)


class BankViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = Bank.objects.all()
    serializer_class = BankSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        hidden = self.request.query_params.get("hidden")

        if hidden:
            queryset = queryset.filter(pk__gt=1)

        return queryset


class SaleOrderViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = SaleOrder.objects.all()
    serializer_class = SaleOrderSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        search_date = self.request.query_params.getlist("search_date[]")
        account_type = self.request.query_params.get("account_type")

        if name:
            queryset = queryset.filter(
                Q(order_no__contains=name) | Q(telephone__contains=name) | Q(customer__name__contains=name) | Q(case_name__contains=name))

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))

        if account_type:
            queryset = queryset.filter(account_type=account_type)

        return queryset


class ReceiptViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = Receipt.objects.all()
    serializer_class = ReceiptSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        search_date = self.request.query_params.getlist("search_date[]")
        if name:
            queryset = queryset.filter(
                Q(order_no__contains=name) | Q(telephone__contains=name) | Q(sale_order__customer__name__contains=name) | Q(
                    case_name__contains=name) | Q(sale_order__order_no__contains=name))

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))
        return queryset


class SaleOrderListAPIView(ListAPIView):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = SaleOrder.objects.all()
    serializer_class = SaleOrderListSerializer

    # def get_queryset(self):
    #     queryset = super().get_queryset()
    #
    #     return queryset


class ExpenseViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = Expense.objects.all()
    serializer_class = ExpenseSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        search_date = self.request.query_params.getlist("search_date[]")
        if name:
            queryset = queryset.filter(
                Q(order_no__contains=name) | Q(name__contains=name))

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))
        return queryset


class ProfitRuleViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    pagination_class = None
    queryset = ProfitRule.objects.all()
    serializer_class = ProfitRuleSerializer


class OtherIncomeViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = OtherIncome.objects.all()
    serializer_class = OtherIncomeSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        search_date = self.request.query_params.getlist("search_date[]")
        if name:
            queryset = queryset.filter(
                Q(order_no__contains=name) | Q(name__contains=name))

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))
        return queryset


class PaymentOrderViewSet(ModelViewSet):
    # authentication_classes = ()
    permission_classes = ()
    queryset = PaymentOrder.objects.all()
    serializer_class = PaymentOrderSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        search_date = self.request.query_params.getlist("search_date[]")
        # print(search_date)
        if name:
            queryset = queryset.filter(Q(order_no__contains=name) | Q(sale_order__order_no__contains=name))

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))
        return queryset


class PayOrderViewSet(ModelViewSet):
    """付款单"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = PayOrder.objects.all()
    serializer_class = PayOrderSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        search_date = self.request.query_params.getlist("search_date[]")
        flag = self.request.query_params.get("flag")
        # print(search_date)
        if name:
            queryset = queryset.filter(person_id=name)

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))

        if flag:
            if flag == "false":
                queryset = queryset.filter(flag=False)
            elif flag == "true":
                queryset = queryset.filter(flag=True)

        return queryset

    def create(self, request, *args, **kwargs):
        if isinstance(request.data, list):  # 批量增加
            serializer = self.get_serializer(data=request.data, many=True)
        else:
            self.serializer_class = PayOrderCreateSerializer
            serializer = self.get_serializer(data=request.data)

        serializer.is_valid(raise_exception=True)
        self.perform_create(serializer)
        headers = self.get_success_headers(serializer.data)
        return Response(serializer.data, status=status.HTTP_201_CREATED, headers=headers)

    def update(self, request, *args, **kwargs):
        partial = kwargs.pop('partial', False)
        if isinstance(request.data, list):
            data = request.data
            ids = [item["id"] for item in data]
            print(self.serializer_class.Meta.model.objects.filter(id__in=ids).query)
            queryset = self.serializer_class.Meta.model.objects.filter(id__in=ids)
            print(queryset, request.data)
            serializer = self.get_serializer(queryset, data=request.data, partial=True, many=True)
            serializer.is_valid(raise_exception=True)
            self.perform_update(serializer)
        else:
            instance = self.get_object()
            serializer = self.get_serializer(instance, data=request.data, partial=partial)
            serializer.is_valid(raise_exception=True)
            self.perform_update(serializer)

            if getattr(instance, '_prefetched_objects_cache', None):
                # If 'prefetch_related' has been applied to a queryset, we need to
                # forcibly invalidate the prefetch cache on the instance.
                instance._prefetched_objects_cache = {}

        return Response(serializer.data)


class JournalViewSet(ReadOnlyModelViewSet):
    """流水账"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = Journal.objects.all()
    serializer_class = JournalSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name")
        order_no = self.request.query_params.get("order_no")
        order_type = self.request.query_params.get("order_type")
        account_type = self.request.query_params.get("account_type")
        account_title = self.request.query_params.get("account_title")

        search_date = self.request.query_params.getlist("search_date[]")
        desc = self.request.query_params.get("desc")

        # print(search_date)
        # 姓名
        if name:
            queryset = queryset.filter(name__contains=name)
        # 单据号
        if order_no:
            queryset = queryset.filter(order_no__contains=order_no)
        # 订单类型
        if order_type:
            queryset = queryset.filter(order_type=int(order_type))
        # 银行账户
        if account_type:
            queryset = queryset.filter(account_type_id=int(account_type))
        # 时间
        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))

        # 科目
        if account_title:
            queryset = queryset.filter(acc_title_id=int(account_title))

        if desc:
            queryset = queryset.filter(desc__contains=desc)

        return queryset


class PaymentDetailViewSet(ReadOnlyModelViewSet):
    """支付明细"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = PaymentDetail.objects.all().order_by('-payment__order_time', 'payment__order_no')
    serializer_class = PaymentDetailSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        lawyer_id = self.request.query_params.get("name")

        search_date = self.request.query_params.getlist("search_date[]")
        # print(search_date)
        # 律师
        if lawyer_id:
            queryset = queryset.filter(lawyer_id=int(lawyer_id))

        # 时间
        if search_date and len(search_date) == 2:
            queryset = queryset.filter(payment__order_time__range=(search_date[0], search_date[1]))
        return queryset


class InitAccountViewSet(ModelViewSet):
    """初始建账"""
    # authentication_classes = ()
    permission_classes = (FinancePermission, )
    pagination_class = None
    queryset = InitAccount.objects.all()
    serializer_class = InitAccountSerializer

    @action(methods=['post'], detail=False)
    def init_data(self, request, *args, **kwargs):
        acc_title = request.data.get("acc_title", None)
        employee = request.data.get("employee", None)
        customer = request.data.get("customer", None)
        bank = request.data.get("bank", None)
        rule = request.data.get("rule", None)
        sale = request.data.get("sale", None)
        receipt = request.data.get("receipt", None)
        expense = request.data.get("expense", None)
        income = request.data.get("income", None)
        borrow = request.data.get("borrow", None)
        lend = request.data.get("lend", None)
        pay = request.data.get("pay", None)
        journal = request.data.get("journal", None)

        print(acc_title, employee, customer, bank, rule, sale, receipt, income, pay, journal, expense)
        # 清空银行账号

        Bank.objects.all().delete()

        data = {
            "id": 1,
            "account": "未结算",
            "money": 0,
            "desc": "未结算账号",
        }
        Bank.objects.create(**data)

        init_datas = InitAccount.objects.all()
        for item in init_datas:
            temp = {
                "account": item.name,
                "money": item.money
            }
            Bank.objects.create(**temp)

        msg = {"status": "success", "message": "数据初始化成功"}
        if journal:
            ret = Journal.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "流水账初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if pay:
            ret = PayOrder.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "付款单初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if income:
            ret = FundBill.objects.filter(category__exact=1).delete()
            if ret[0] < 0:
                msg["message"] = "其他收入初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if expense:
            ret = FundBill.objects.filter(category__exact=0).delete()
            if ret[0] < 0:
                msg["message"] = "费用单初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if borrow:
            ret = FundBill.objects.filter(category__exact=2).delete()
            if ret[0] < 0:
                msg["message"] = "资金借入初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if lend:
            ret = FundBill.objects.filter(category__exact=3).delete()
            if ret[0] < 0:
                msg["message"] = "资金借出初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if receipt:
            ret = Receipt.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "收款单初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)
        if sale:
            ret = SaleOrder.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "销售单初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if rule:
            ret = ProfitRule.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "利润分配规则初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)
        if customer:
            ret = Customer.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "客户信息初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if employee:
            ret = Employee.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "员工信息初始化失败!"
                msg["status"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        if acc_title:
            ret = AccountTitle.objects.all().delete()
            if ret[0] < 0:
                msg["message"] = "会计科目初始化失败!"
                msg["status_code"] = "fail"
                return Response(data=msg, status=status.HTTP_200_OK)

        return Response(data=msg, status=status.HTTP_200_OK)


class BorrowLendViewSet(ModelViewSet):
    """借入借出"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = BorrowLend.objects.all()
    serializer_class = BorrowLendSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name", None)
        category = self.request.query_params.get("category", None)

        search_date = self.request.query_params.getlist("search_date[]", None)

        if category:
            queryset = queryset.filter(category=int(category))

        if name:
            queryset = queryset.filter(person__name__contains=name)
        # 时间
        if search_date and len(search_date) == 2:
            queryset = queryset.filter(oper_time__range=(search_date[0], search_date[1]))
        return queryset


class FundBillViewSet(ModelViewSet):
    """资金账单"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = FundBill.objects.all()
    serializer_class = FundBillSerializer

    def get_queryset(self):
        queryset = super().get_queryset()
        name = self.request.query_params.get("name", None)
        category = self.request.query_params.get("category", None)

        search_date = self.request.query_params.getlist("search_date[]", None)
        acc_title = self.request.query_params.get("acc_title", None)

        if category:
            queryset = queryset.filter(category=int(category))

        if acc_title:
            queryset = queryset.filter(acc_title_id=int(acc_title))

        if name:
            queryset = queryset.filter(Q(employee__name__contains=name) | Q(order_no__contains=name))
        # 时间
        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))
        return queryset

    @action(methods=['get'], detail=False)
    def user_bills(self, request, *args, **kwargs):
        category = request.query_params.get("category", None)
        employee = request.query_params.get("employee", None)
        if category and employee:
            queryset = self.get_queryset().filter(category=int(category), employee_id=int(employee), money__gt=F("repayment"))

            serializer = self.get_serializer(instance=queryset, many=True)

            return Response(data=serializer.data, status=status.HTTP_200_OK)
        else:
            msg = {"status_code": status.HTTP_404_NOT_FOUND, "message": "查询失败!"}
            return Response(data=msg, status=status.HTTP_404_NOT_FOUND)


class RepaymentBillViewSet(ModelViewSet):
    """还款记录"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = RepaymentBill.objects.all()
    serializer_class = RepaymentBillSerializer

    def create(self, request, *args, **kwargs):
        if isinstance(request.data, list):  # 批量增加
            serializer = self.get_serializer(data=request.data, many=True)
        else:
            serializer = self.get_serializer(data=request.data)

        serializer.is_valid(raise_exception=True)
        self.perform_create(serializer)
        headers = self.get_success_headers(serializer.data)
        return Response(serializer.data, status=status.HTTP_201_CREATED, headers=headers)


class ProfitStats(APIView):
    """利润统计"""
    authentication_classes = ()
    permission_classes = ()

    def get(self, request, *args, **kwargs):
        # 主营业务收入(和律师有关的)
        ym = request.query_params.get('search_date', None)
        d_ym = datetime.datetime.strptime(ym, '%Y-%m')

        queryset0 = SaleOrder.objects.filter(order_time__year=d_ym.year, order_time__month=d_ym.month)
        results0 = queryset0.values("acc_title__code", "acc_title", "acc_title__name")\
            .order_by("acc_title__code", "acc_title")\
            .annotate(total_money=Sum("recv_money"))

        # 其他收入 category=1 其他收入
        queryset1 = FundBill.objects.filter(category=1, order_time__year=d_ym.year, order_time__month=d_ym.month)
        results1 = queryset1.values("acc_title__code", "acc_title", "acc_title__name") \
            .order_by("acc_title__code", "acc_title") \
            .annotate(total_money=Sum("money"))

        # 律师费支出
        queryset = PayOrder.objects.filter(sale_order__order_time__year=d_ym.year, sale_order__order_time__month=d_ym.month)
        out_results0 = queryset.values("acc_title__code", "acc_title", "acc_title__name") \
            .order_by("acc_title__code", "acc_title") \
            .annotate(total_money=Sum("money"))

        queryset = FundBill.objects.filter(category=0, order_time__year=d_ym.year, order_time__month=d_ym.month)
        out_results1 = queryset.values("acc_title__code", "acc_title", "acc_title__name") \
            .order_by("acc_title__code", "acc_title") \
            .annotate(total_money=Sum("money"))

        results0 = results0.union(results1, out_results0, out_results1)
        # print(results0)
        # 年统计
        year_results0 = SaleOrder.objects.filter(order_time__year=d_ym.year, order_time__month__lte=d_ym.month)\
            .values("acc_title__code", "acc_title", "acc_title__name")\
            .order_by("acc_title__code", "acc_title")\
            .annotate(year_total_money=Sum("recv_money"))

        # 其他收入 category=1 其他收入
        year_results1 = FundBill.objects.filter(category=1, order_time__year=d_ym.year, order_time__month__lte=d_ym.month)\
            .values("acc_title__code", "acc_title", "acc_title__name") \
            .order_by("acc_title__code", "acc_title") \
            .annotate(year_total_money=Sum("money"))

        # 律师费支出
        out_year_results0 = PayOrder.objects.filter(sale_order__order_time__year=d_ym.year, sale_order__order_time__month__lte=d_ym.month)\
            .values("acc_title__code", "acc_title", "acc_title__name") \
            .order_by("acc_title__code", "acc_title") \
            .annotate(year_total_money=Sum("money"))

        out_year_results1 = FundBill.objects.filter(category=0, order_time__year=d_ym.year, order_time__month__lte=d_ym.month)\
            .values("acc_title__code", "acc_title", "acc_title__name") \
            .order_by("acc_title__code", "acc_title") \
            .annotate(year_total_money=Sum("money"))

        year_results0 = year_results0.union(year_results1, out_year_results0, out_year_results1)
        # print(year_results0)
        # 科目列表
        titles = AccountTitle.objects.filter(code__isnull=False).values('code', 'name', acc_title=F("id")).order_by("code", "id")

        if not titles.exists():
            return Response(list())

        df_titles = pd.DataFrame(list(titles))
        # 月统计
        # df_result = pd.DataFrame()
        if results0.exists():
            df_month = pd.DataFrame(list(results0))
            df_month.rename(columns={"acc_title__code": "code", "acc_title__name": "acc_title_name"}, inplace=True)
            df_month = df_month.groupby(['code', 'acc_title', 'acc_title_name'], as_index=False).agg({'total_money': 'sum'})
            # print("df_month: ", df_month)

            df_sub = df_month.copy()
            df_sub['code'] = df_sub['code'].str.slice(0, -2)
            sub_stats = df_sub.groupby(['code'], as_index=False).agg({'total_money': 'sum'})
            # print("sub_stats: ", sub_stats)

            df_main = sub_stats[sub_stats['code'].str.len() == 4].copy()
            df_main['code'] = df_main['code'].str.slice(0, 2)
            main_stats = df_main.groupby(['code'], as_index=False).agg({'total_money': 'sum'})
            # print("main_stats: ", main_stats)

            df_month = df_month.loc[:, ["code", "total_money"]]
            df_stats = pd.concat([main_stats, sub_stats, df_month])

            df_stats = df_stats.sort_values('code')
            # print("df_stats: ", df_stats)

            df_titles = pd.merge(df_titles, df_stats, on="code", how="left")

        # print("Month: ", df_titles)
        # 年统计
        if year_results0.exists():
            df_year = pd.DataFrame(list(year_results0))
            df_year.rename(columns={"acc_title__code": "code", "acc_title__name": "acc_title_name"}, inplace=True)
            df_year = df_year.groupby(['code', 'acc_title', 'acc_title_name'], as_index=False).agg({'year_total_money': 'sum'})
            print("df_year: ", df_year)

            df_year_sub = df_year.copy()
            df_year_sub['code'] = df_year_sub['code'].str.slice(0, -2)
            year_sub_stats = df_year_sub.groupby(['code'], as_index=False).agg({'year_total_money': 'sum'})

            df_year_main = year_sub_stats[year_sub_stats['code'].str.len() == 4].copy()
            df_year_main['code'] = df_year_main['code'].str.slice(0, 2)
            year_main_stats = df_year_main.groupby(['code'], as_index=False).agg({'year_total_money': 'sum'})

            df_year = df_year.loc[:, ["code", "year_total_money"]]
            df_year_stats = pd.concat([year_main_stats, year_sub_stats, df_year])
            df_year_stats = df_year_stats.sort_values('code')

            df_titles = pd.merge(df_titles, df_year_stats, on="code", how="left")

        df_titles.fillna(0, inplace=True)

        if not {'total_money', 'year_total_money'}.issubset(df_titles.columns):
            df_titles['total_money'] = 0
            df_titles['year_total_money'] = 0

        df_totals = df_titles.loc[df_titles['code'].str.len() == 2, ['code', 'total_money', 'year_total_money']]
        df_totals.set_index("code", inplace=True)

        ser_totals = df_totals.loc["01", :]

        df_totals = df_totals.drop('01')
        ser_totals_stats = df_totals.sum()

        ser_totals = ser_totals - ser_totals_stats

        ser_totals['code'] = '99'
        ser_totals['name'] = '营业利润'

        df_totals_temp = pd.DataFrame(ser_totals)

        df_titles = pd.concat([df_titles, df_totals_temp.T])
        df_titles.fillna(0, inplace=True)

        return Response(df_titles.to_dict("records"))


class BusinessFeeViewSet(ModelViewSet):
    """商务费"""
    # authentication_classes = ()
    permission_classes = ()
    queryset = BusinessFee.objects.all()
    serializer_class = BusinessFeeSerializer
    pagination_class = None


class Hanzi2Pinyin(APIView):
    """汉字转拼音"""
    # authentication_classes = ()

    def get(self, request):
        name = request.query_params.get("name", None)
        name_pinyin = pinyin(name, style=Style.FIRST_LETTER)
        name_pinyin = [item[0] for item in name_pinyin]

        data = {
            'help_code': ''.join(name_pinyin).upper()
        }
        return Response(data)
